﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using ERPStudio;
using System.Data.SqlClient;
using System.IO;
using System.Globalization;
using System.Data.OleDb;
using System.Diagnostics;
using System.Threading;
using System.Reflection;

namespace ESRP
{
    public partial class ImportFrm : FrmRezumat
    {
        public class FileNodeInfo
        {
            public String NumeUnitate = String.Empty;
            public int An = DateTime.Now.Year;
            public int Luna = DateTime.Now.Month;
            public Boolean seImporta = false;
            public FileNodeInfo(String id)
            {
                NumeUnitate = id;
            }
            public FileNodeInfo(String id,int vAn,int vLuna)
            {
                NumeUnitate = id;
                An = vAn;
                Luna = vLuna;
            }


        }
        ToolStripProgressBar pBar = null;

        private String TipUnitate = String.Empty;
        private int LunaCurenta = DateTime.Now.Month;
        private int AnulCurent = DateTime.Now.Year;

        public ImportFrm(ModuleClass mInfo)
            : base(mInfo)
        {
            InitializeComponent();
            workConnString = mInfo.workConnectionString;
        }

        private void ImportFrm_Load(object sender, EventArgs e)
        {
            pBar = AddProgressBar("ProgressBar1", ToolStripItemAlignment.Left, 100);
            SqlConnection con = DeschideConexiune(workConnString);
            jurnaleTableAdapter.Connection = con;
            jurnaleTableAdapter.Fill(eSRPDataSet.Jurnale, ModuleInfo.CodUnitate);
            Object ret = GetSetupValue("BalconSetup", "TipUnitate", ModuleInfo.CodUnitate, con);
            String val = String.Empty;
            if (ret == null || ret == DBNull.Value)
            {
                while (ret == null)
                {
                    
                    SqlCommand comcfg = new SqlCommand("INSERT INTO BalconSetup (CodUnitate,AnCurent,LunaCurenta) VALUES (" + ModuleInfo.CodUnitate.ToString() +"," + DateTime.Now.Year.ToString() + "," + DateTime.Now.AddMonths(-1).Month.ToString() + ")",
                        con);
                    comcfg.ExecuteNonQuery();
                    ret = GetSetupValue("BalconSetup", "TipUnitate", ModuleInfo.CodUnitate, con);
                }
            }
            TipUnitate = ret.ToString();
            ret = GetSetupValue("BalconSetup", "LunaCurenta", ModuleInfo.CodUnitate, con);
            if (ret != null && ret != DBNull.Value)
                LunaCurenta = Convert.ToInt16(ret);
            ret = GetSetupValue("BalconSetup", "AnCurent", ModuleInfo.CodUnitate, con);
            if (ret != null && ret != DBNull.Value)
                AnulCurent = Convert.ToInt16(ret);
            InchideConexiune(con);
            
            if (TipUnitate == "B")
            {
                CompleteazaCB.Visible = true;
                ImportValutaCB.Visible = true;
                PastreazaCB.Visible = true;
                NuConturiCB.Visible = false;
                NuNoteCB.Visible = false;

            }
            else
            {
                CompleteazaCB.Visible = false ;
                ImportValutaCB.Visible = false;
                PastreazaCB.Visible = false;
                NuConturiCB.Visible = true;
                NuNoteCB.Visible = true;
            }
            PerioadaCurentaDTP.CustomFormat = "MM/yyyy";
            PerioadaCurentaDTP.Value = new DateTime(AnulCurent, LunaCurenta, 1);
            if (eSRPDataSet.Jurnale.Count == 0)
            {
                jurnalL.Visible = false;
                jurnalCB.Visible = false;
            }
            else
                jurnalCB.SelectedIndex = 0;
            if (!EsteArhiva())
            {
                AddButton("In calea...", global::ERPStudio.Properties.Resources.salvare1, InCalea, ToolStripItemAlignment.Right, true);
                AddButton("Import", global::ERPStudio.Properties.Resources._001_07, Import, ToolStripItemAlignment.Right, false);
                //AddButton("Import SQL", global::ERPStudio.Properties.Resources._001_37, Import_SQL, ToolStripItemAlignment.Right, false);
            }
        }
        
        private void InCalea(object sender, EventArgs e)
        { 
            openFileDialog1.AddExtension = true;
            openFileDialog1.FilterIndex = 1;
            openFileDialog1.RestoreDirectory = true;
            openFileDialog1.FileName = "*.dbf";
            openFileDialog1.DefaultExt = ".dbf";
            openFileDialog1.Filter = "FoxPro files (*.dbf)|*.dbf";
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                FileInfo fi = new FileInfo(openFileDialog1.FileName);
                ProceseazaDirectorBalans(fi.DirectoryName);
            }
            SetStatus("Scanat director...OK");
        }

        private void ProceseazaDirectorBalans(String cale)
        { 
             ///Cauta daca exista directorul date
             ///e_date = daca exista directorul date
             ///e_cfg = daca exista directorul de configurare
	        String cl = cale+"\\Date",unit=String.Empty,nume_fcfg=String.Empty;
            if(!Directory.Exists(cl))
            {
                MessageBox.Show("Lipseste directorul Date = " + cl);
                return;
            }
            else
            {
		        nume_fcfg = cale+"\\balans.dbf";
		        if(!File.Exists(nume_fcfg))
                {
			       MessageBox.Show("Lipseste fisierul balans.dbf");
                   return; 
                }
            }
		
            cale = cale.Trim();
            this.FisiereTV.Nodes.Clear();
            TreeNode tnRoot = this.FisiereTV.Nodes.Add(cale);
            //tnRoot.Tag = new NodeInfo(cod);
            String strItem = String.Empty;
            String ConnectionString = "Provider=VFPOLEDB.1;" + "Data Source = " + cale  +";";
            System.Data.OleDb.OleDbConnection dBaseConnection = null;
            dBaseConnection = new System.Data.OleDb.OleDbConnection(ConnectionString);
            System.Data.OleDb.OleDbDataReader dbCfg = null;
            System.Data.OleDb.OleDbDataReader dbRul = null;
            int m_nIndex = 0,vanarh=0;
            try
            {
                dBaseConnection.Open();
                System.Data.OleDb.OleDbCommand dBaseCommand= new System.Data.OleDb.OleDbCommand("SELECT * FROM [balans]", dBaseConnection);
                dbCfg = dBaseCommand.ExecuteReader();
                if(dbCfg==null){
                    return;
                }
                while (dbCfg.Read())
                {
                    strItem = dbCfg["PATH_DATE"].ToString().Trim();
                    strItem = strItem.Trim();
                    strItem = strItem.ToUpper();
                    strItem = strItem.Replace("\\BALANS",cale);
                    FileNodeInfo info = new FileNodeInfo(strItem);
                    TreeNode addNode = tnRoot.Nodes.Add(dbCfg["TITL_1"].ToString().Trim());
                    addNode.Tag = info;
                    SearchAnArhive(addNode, strItem, dbCfg["TITL_1"].ToString(), m_nIndex,dbCfg["LUNa_CLO"].ToString(), ref vanarh);
                    /*nume_fcfg = "CONT.DBF";
                    ConnectionString = "Provider=VFPOLEDB.1;Data Source = " + strItem + ";";
                    if (dBaseConnection2 != null && dBaseConnection2.State == ConnectionState.Open)
                        dBaseConnection2.Close();
                    if (dBaseConnection2!= null) 
                        dBaseConnection2.Dispose();
                    dBaseConnection2 = new System.Data.OleDb.OleDbConnection(ConnectionString);
                    dBaseConnection2.Open();
                    dBaseCommand = new System.Data.OleDb.OleDbCommand("SELECT * FROM [rul]", dBaseConnection2);
                    dbRul = dBaseCommand.ExecuteReader();
                    if (dbRul == null)
                    {
                        return;
                    }
                    TreeNode contNode = addNode.Nodes.Add(nume_fcfg);
                    FileNodeInfo infoCont = new FileNodeInfo("");
                    //daca are o inregistrare
                    if (dbRul.Read())
                    {
                        infoCont.An = Convert.ToDateTime(dbRul["Data"]).Year;
                        infoCont.Luna = Convert.ToDateTime(dbRul["Data"]).Month;
                    }
                    else
                    {
                        infoCont.Luna = Convert.ToInt32(dbCfg["Luna_clo"]);
                        if (infoCont.Luna==1)
                            vanarh++;
                        infoCont.An = vanarh;
                    }
                    contNode.Tag = infoCont;
                    dbRul.Close();*/
                }
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return;
            }
            finally
            {
                if(dbCfg!=null)
                    dbCfg.Close();
                if (dbRul != null)
                    dbRul.Close();
                dBaseConnection.Close();
            }
            tnRoot.ExpandAll();

        }

        private void SearchAnArhive(TreeNode nod, String cale, String NumeUnit,int UParinte,String luna_clo, ref int vanarh)
        {
            vanarh = 0;
            try
            {
                DirectoryInfo di = new DirectoryInfo(cale);
                DirectoryInfo[] dirs = di.GetDirectories("Arh*.*");
                int an = 0, maxan = 0;
                foreach (DirectoryInfo diNext in dirs)
                {
                    String numef = diNext.Name;
                    String nume_cale_noua = cale + diNext.Name;
                    numef = numef.ToLower();
                    numef = numef.Replace("arh", String.Empty);
                    if (numef == "iva")
                    {
                        an = 0;
                        nume_cale_noua = "PERIOADA CURENTA(" + luna_clo + ")";
                    }
                    else
                        if (numef.Length == 4)
                            an = Convert.ToInt32(numef);
                        else
                            continue;
                    maxan = System.Math.Max(maxan, an);
                    if (an == 0)
                        an = maxan + 1;
                    vanarh = maxan + 1;
                    TreeNode na = nod.Nodes.Add(nume_cale_noua);
                    FileNodeInfo infoCont = new FileNodeInfo(cale + diNext.Name);
                    infoCont.An = an;
                    infoCont.seImporta = true;
                    na.Tag = infoCont;
                    //SearchLuniArhive(na, nume_cale_noua + "\\", an, NumeUnit, UParinte);
                }
            }
            catch (DirectoryNotFoundException)
            {
                return;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
            }

        }

        private void SearchLuniArhive(TreeNode pItem, String cale,int an, String unitate,int UParinte)
        {
            DirectoryInfo di = new DirectoryInfo(cale);
            FileInfo[] fi = di.GetFiles("Rul*.arh");
            int luna = 0;
            String ConnectionString = String.Empty;
            System.Data.OleDb.OleDbConnection dBaseConnection = null;
            System.Data.OleDb.OleDbDataReader dbRul = null;
            foreach (FileInfo fiTemp in fi)
            {
                String numef = fiTemp.Name;
                String numefr = fiTemp.Name;
                numefr = numefr.ToLower();
                numefr = numefr.Replace(".arh", string.Empty);
                numef = numef.ToLower();
                String nume_fis_cont = numef;
                nume_fis_cont = nume_fis_cont.ToUpper();
                nume_fis_cont = nume_fis_cont.Replace("RUL", "CONT");
                String nume_cale_noua = cale + fiTemp.Name;
                numef = numef.Replace("rul", String.Empty);
                numef = numef.Replace(".arh", String.Empty);
                luna = Convert.ToInt32(numef);
                nume_cale_noua = nume_cale_noua.Trim();
                //deschidem fisierul cu rulaje
                ConnectionString = "Provider=VFPOLEDB.1;" + "Data Source = " + cale + ";";
                dBaseConnection = new System.Data.OleDb.OleDbConnection(ConnectionString);
                try
                {
                    dBaseConnection.Open();
                    System.Data.OleDb.OleDbCommand dBaseCommand = new System.Data.OleDb.OleDbCommand("SELECT * FROM [" + fiTemp.Name + "]", dBaseConnection);
                    dbRul = dBaseCommand.ExecuteReader();
                    if (dbRul == null)
                    {
                        return;
                    }
                    TreeNode contNode = pItem.Nodes.Add(nume_cale_noua + "+" + nume_fis_cont);
                    FileNodeInfo infoCont = new FileNodeInfo("");
                    //daca are o inregistrare
                    if (dbRul.Read())
                    {
                        infoCont.An = Convert.ToDateTime(dbRul["Data"]).Year;
                        if (infoCont.An < 1950)
                            infoCont.An += 100;
                        infoCont.Luna = Convert.ToDateTime(dbRul["Data"]).Month;
                    }
                    else
                    {
                        infoCont.Luna = luna;
                        infoCont.An = an;
                    }
                    contNode.Tag = infoCont;
                    dbRul.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                    return;
                }
                finally
                {
                    if (dbRul != null)
                        dbRul.Close();
                    dBaseConnection.Close();
                }
            }

        }

        private void Import(object sender, EventArgs e)
        {
            #region Pagina2
            if (tabPage2 == tabControl.SelectedTab)
            {
                if (ServerTB.Text == string.Empty || BazaDateTB.Text == String.Empty)
                {
                    MessageBox.Show("Completati serverul si baza de date!",
                            "Stop",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Stop);
                    return;
                }
                SqlCommand cmd = new SqlCommand("ImportVersiuneAnterioara");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("ServerName", ServerTB.Text));
                cmd.Parameters.Add(new SqlParameter("DBName", BazaDateTB.Text));
                cmd.Parameters.Add(new SqlParameter("CodUnitate", ModuleInfo.CodUnitate.ToString()));
                try
                {
                    cmd.Connection = DeschideConexiune(workConnString);
                    cmd.ExecuteNonQuery();
                    InchideConexiune(cmd.Connection);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message,
                        "Eroare",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
                    return;
                }
                MessageBox.Show("Datele au fost preluate corect!",
                        "Info",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
                return;
            }
            
            #endregion

            #region Pagina3
            if (tabPage3 == tabControl.SelectedTab)
            {
                if (BazaWTB.Text == String.Empty)
                {
                    MessageBox.Show("Completati baza de date!",
                            "Stop",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Stop);
                    return;
                }

                if (codUnitateCB.Text == String.Empty)
                {

                    MessageBox.Show("Va rugam selectati un cod de unitate!",
                        "Info",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
                    return;
                }


                SqlCommand cmd = new SqlCommand("balcon_ImportBalansW", DeschideConexiune(workConnString));
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("ServerName", ServerTB.Text));
                cmd.Parameters.Add(new SqlParameter("DBName", BazaDateTB.Text));
                cmd.Parameters.Add(new SqlParameter("CodUnitate", ModuleInfo.CodUnitate.ToString()));
                cmd.Parameters.Add(new SqlParameter("CodNod", codUnitateCB.Text));
                cmd.ExecuteNonQuery();
                InchideConexiune(cmd.Connection);
                MessageBox.Show("Datele au fost preluate corect!",
                        "Info",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Information);
                return;
            } 
            #endregion


            if (MessageBox.Show("Atentie datele din unitatea curenta se vor suprascrie! Continuati?",
                    "Avertisment",
                    MessageBoxButtons.YesNo,
                    MessageBoxIcon.Stop) == DialogResult.No)
                return;

            if (FisiereTV.SelectedNode == null)
            {
                MessageBox.Show("Va rugam selectati o cale valida!",
                        "Eroare",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Stop);
                return;
            }
            FileNodeInfo fin = FisiereTV.SelectedNode.Tag as FileNodeInfo;
            if (fin==null || !fin.seImporta) 
            {
                MessageBox.Show("Va rugam selectati o cale valida pentru import!",
                        "Eroare",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Stop);
                return;
            }
            SqlConnection con = DeschideConexiune(workConnString);
            

            if (FisiereTV.SelectedNode.Text.ToUpper().Contains("PERIOADA CURENTA"))
            {
                if(!NuConturiCB.Checked)
                    ImportCont(fin.NumeUnitate, con);
                if (!NuNoteCB.Checked)
                {
                    ImportRulaje(fin.NumeUnitate, con, true,false,String.Empty);
                    DirectoryInfo di = new DirectoryInfo(fin.NumeUnitate);
                    FileInfo[] fi = di.GetFiles("Rul*.arh");
                    foreach (FileInfo fiTemp in fi)
                    {
                        ImportRulaje(fin.NumeUnitate, con, false, true, fiTemp.Name);
                    }
                }
            }
            InchideConexiune(con);
        }

        private Boolean ImportCont(String Vcale, SqlConnection con)
        {
            pBar.Value = 0;
            String Cont_Parinte = String.Empty, sint = String.Empty, an1 = String.Empty, an2 = String.Empty;
            System.Data.OleDb.OleDbConnection dBaseConnection = null;
            System.Data.OleDb.OleDbDataReader dbCont = null;

            SqlTransaction tran = con.BeginTransaction();
            SqlCommand cmd = new SqlCommand("", con);
            
            

            cmd.Transaction = tran;

            String cale = Vcale.Replace("ARHIVA", String.Empty);
            String ConnectionString = "Provider=VFPOLEDB.1;Data Source = " + cale + ";";
            dBaseConnection = new System.Data.OleDb.OleDbConnection(ConnectionString);
            dBaseConnection.Open();
            OleDbCommand dBaseCommand = new System.Data.OleDb.OleDbCommand("", dBaseConnection);
            dBaseCommand.CommandText = "SELECT * FROM [cont] ORDER BY [cont_sin],[cont_an1],[Cont_an2]";
            try
            {
                cmd.CommandText = "DELETE FROM [ConturiSpeciale] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
                cmd.ExecuteNonQuery();
                cmd.CommandText = "DELETE FROM [ConturiCoresp] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
                cmd.ExecuteNonQuery();
                cmd.CommandText = "DELETE FROM [RulajeDoc] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
                cmd.ExecuteNonQuery();
                cmd.CommandText = "DELETE FROM [Rulaje] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
                cmd.ExecuteNonQuery();

                cmd.CommandText = "ALTER TABLE dbo.Articole NOCHECK CONSTRAINT FK_Articole_ConturiCheltuiala";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "ALTER TABLE dbo.Articole NOCHECK CONSTRAINT FK_Articole_Conturi";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "DISABLE Trigger ActSolduri ON Conturi";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "DISABLE Trigger ContActTerminalDelete ON Conturi";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "DISABLE Trigger ContActTerminalModify ON Conturi";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "DELETE FROM [Conturi] WHERE Cont<>'N/A' AND [CodUnitate]=" + ModuleInfo.CodUnitate.ToString();    
                cmd.ExecuteNonQuery();
                


                #region Adaugare NOD N/A
                cmd.CommandText = "SELECT COUNT(*) FROM [Conturi] WHERE Cont='N/A' AND [CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
                Int32 cate = Convert.ToInt32(cmd.ExecuteScalar());
                if (cate == 0)
                {
                    cmd.CommandText = "INSERT INTO [Conturi]([Cont],[CodUnitate],[Denumire],[Tip],[Terminal],[ContParinte],[EsteAnalitic],[Categorie]) VALUES ('N/A'," + ModuleInfo.CodUnitate.ToString() + ",'Principal','B',0,'N/A',1,0)";
                    cmd.ExecuteNonQuery();
                }  
                #endregion

                



                dbCont = dBaseCommand.ExecuteReader();
                if (dbCont == null)
                {
                    return true;
                }
                //daca are o inregistrare
                pBar.Maximum = dbCont.RecordsAffected;
                while (dbCont.Read())
                {
                    sint = dbCont["cont_sin"].ToString();
                    an1 = dbCont["cont_an1"].ToString().Trim();
                    an2 = dbCont["cont_an2"].ToString().Trim();
                    if (Convert.ToInt16(an1) == 0)
                        an1 = String.Empty;
                    if (Convert.ToInt16(an2) == 0)
                        an2 = String.Empty;
                    if (Convert.ToInt16(dbCont["tip_cont"]) == -1)
                        Cont_Parinte = sint;
                    String den = dbCont["expl"].ToString().Trim();
                    if (den == String.Empty)
                        den = dbCont["denumire"].ToString().Trim();
                    den = den.Replace("'", "''");
                    Int16 tip = Convert.ToInt16(dbCont["tip_cont"]);
                    String func = dbCont["functiune"].ToString().Trim();
                    String numeC = sint + (an1 != String.Empty ? "." + an1 : String.Empty) + (an2 != String.Empty ? "." + an2 : String.Empty);
                    cmd.CommandText = "INSERT INTO [Conturi] ([Cont],[CodUnitate],[Denumire],[Tip],[Terminal],[ContParinte],[EsteAnalitic],[DebitInitial],[CreditInitial],[DebitPrecedent],[CreditPrecedent],[Categorie],[RulajDebit],[RulajCredit]) VALUES ('" +
                        numeC + "'," +
                        this.ModuleInfo.CodUnitate.ToString() + ",'" +
                        den + "','" +
                        (func != "X" ? func : "A") + "'," +
                        ((tip == 1 || tip == 0) ? "1" : "0") + ",'" +
                        ((tip == -1 || tip == 0) ? "N/A" : Cont_Parinte) + "'," +
                        (tip == 1 ? "1" : "0") + "," +
                        Convert.ToDouble(dbCont["deb_init"]).ToString("F2", CultureInfo.InvariantCulture) + "," +
                        Convert.ToDouble(dbCont["cre_init"]).ToString("F2", CultureInfo.InvariantCulture) + ",0,0," +
                        (func == "X" ? "2" : "0") +
                        ",0,0)";
                    cmd.ExecuteNonQuery();
                    pBar.Value++; // CultureInfo.InvariantCulture
                    Application.DoEvents();
                }
                tran.Commit();
                cmd.CommandText = "balcon_UpdateSoldCont";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Clear();
                cmd.Parameters.Add(new SqlParameter("CodUnitate", ModuleInfo.CodUnitate));
                cmd.ExecuteNonQuery();


                cmd.CommandText = "ENABLE Trigger ActSolduri ON Conturi";
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                cmd.CommandText = "ENABLE Trigger ContActTerminalDelete ON Conturi";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "ENABLE Trigger ContActTerminalModify ON Conturi";
                cmd.ExecuteNonQuery();

                cmd.CommandText = "ALTER TABLE dbo.Articole CHECK CONSTRAINT FK_Articole_ConturiCheltuiala";
                cmd.ExecuteNonQuery();
                cmd.CommandText = "ALTER TABLE dbo.Articole CHECK CONSTRAINT FK_Articole_Conturi";
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message.ToString() + ",\n" + cmd.CommandText);
                tran.Rollback();
                return false ;
            }
            finally
            {
                if (dbCont != null)
                    dbCont.Close();
                dBaseConnection.Close();
            }


            dbCont.Close();
            dBaseConnection.Close();
            return true;
        
        }

        private Boolean ImportRulaje(String Vcale, SqlConnection con,Boolean stergeEx,Boolean arhiva,String tabela)
        {
            pBar.Value = 0;
            String Cont_Parinte = String.Empty, sintD = String.Empty, an1D = String.Empty, an2D = String.Empty, sintC = String.Empty, an1C = String.Empty, an2C = String.Empty;
            System.Data.OleDb.OleDbConnection dBaseConnection = null;
            System.Data.OleDb.OleDbDataReader dbRul = null;

            SqlTransaction tran = con.BeginTransaction();
            SqlCommand cmd = new SqlCommand("", con);
            cmd.CommandText = "DELETE FROM [Rulaje] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
            cmd.Transaction = tran;
            NumeFileTB.Text = Vcale + "\\" +tabela;
            String cale = String.Empty;
            if (!arhiva)
                cale = Vcale.Replace("ARHIVA", String.Empty);
            else
                cale = Vcale;
            String ConnectionString = "Provider=VFPOLEDB.1;Data Source = " + cale + ";";
            dBaseConnection = new System.Data.OleDb.OleDbConnection(ConnectionString);
            dBaseConnection.Open();
            OleDbCommand dBaseCommand = new System.Data.OleDb.OleDbCommand("", dBaseConnection);
            dBaseCommand.CommandText = "SELECT * FROM ["+
                (arhiva?tabela:"rul")+"] ORDER BY [doc],[data]";
            try
            {
                if (stergeEx)
                {
                    cmd.ExecuteScalar();
                    cmd.CommandText = "DELETE FROM [RulajeDoc] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
                    cmd.ExecuteScalar();
                }
                dbRul = dBaseCommand.ExecuteReader();
                if (dbRul == null)
                {
                    return true;
                }
                //daca are o inregistrare
                pBar.Maximum = dbRul.RecordsAffected;
                Decimal Suma =Decimal.Zero;
                Decimal SumaValuta =Decimal.Zero;
                String CodOperatiune = String.Empty;
                Int64 IDDocument = 0;
                Int16 NrCrt = 0;
                while (dbRul.Read())
                {
                    sintD = dbRul["deb_sin"].ToString();
                    an1D = dbRul["deb_an1"].ToString().Trim();
                    an2D = dbRul["deb_an2"].ToString().Trim();

                    sintC = dbRul["cre_sin"].ToString();
                    an1C = dbRul["cre_an1"].ToString().Trim();
                    an2C = dbRul["cre_an2"].ToString().Trim();

                    if (Convert.ToInt16(an1D) == 0)
                        an1D = String.Empty;
                    if (Convert.ToInt16(an2D) == 0)
                        an2D = String.Empty;

                    if (Convert.ToInt16(an1C) == 0)
                        an1C = String.Empty;
                    if (Convert.ToInt16(an2C) == 0)
                        an2C = String.Empty;
                    String numeC = sintC + (an1C != String.Empty ? "." + an1C : String.Empty) + (an2C != String.Empty ? "." + an2C : String.Empty);
                    String numeD = sintD + (an1D != String.Empty ? "." + an1D : String.Empty) + (an2D != String.Empty ? "." + an2D : String.Empty);
                    if (numeC == "0")
                        numeC = "N/A";
                    if (numeD == "0")
                        numeD = "N/A";

                    DateTime data = Convert.ToDateTime(dbRul["data"]);
                    Int16 model = Convert.ToInt16(dbRul["model"]);
                    model -= 1;
                    if (model > 3)
                        model = 3;
                    switch (model)
                    { 
                        case 0:
                            model = 1;
                            break;
                        case 1:
                            model = 0;
                            break;
                    }
                    if (CodOperatiune != dbRul["cod_oper"].ToString().Trim())
                    {
                        if (CodOperatiune != String.Empty)
                        {
                            cmd.CommandText = "UPDATE [RulajeDoc]  SET [ValoareValuta] = " +
                              Convert.ToDouble(SumaValuta).ToString("F2", CultureInfo.InvariantCulture) + ", [ValoareLei]=" +
                              Convert.ToDouble(Suma).ToString("F2", CultureInfo.InvariantCulture) + " WHERE [IDDocument] = " + IDDocument.ToString();
                            cmd.ExecuteNonQuery();
                            
                            Suma = Decimal.Zero;
                            SumaValuta = Decimal.Zero;
                        }
                        CodOperatiune = dbRul["cod_oper"].ToString().Trim();
                        /// nu se imoprta decat inregistrari din anul curent!
                        if (data.Year != AnulCurent)
                            break;
                        /// modifica totalul la documentul anterior
                        cmd.CommandText = "INSERT INTO [RulajeDoc] ([ValoareLei],[ValoareValuta],[NumarDoc],[DataDoc],[NumarNota],[TipFormula],[IDJurnal],[Observatii],[xUser],[xHost],[xDate],[CodUnitate]) VALUES (0.00,0.00," +
                            dbRul["doc"].ToString().Trim() + ",'" +
                            data.Year.ToString() + "-" + data.Month.ToString() + "-" + data.Day.ToString() + "',''," +
                            model.ToString() + ","+
                            (jurnalCB.SelectedValue!=null?jurnalCB.SelectedValue.ToString():"0") + ",'',system_user,host_name(),getdate()," +
                            ModuleInfo.CodUnitate.ToString() + "); SELECT [IDDocument] FROM [RulajeDoc] WHERE (IDDocument = SCOPE_IDENTITY())";
                        Object ret = cmd.ExecuteScalar();
                        Trace.Assert(ret != null && ret != DBNull.Value);
                        if (ret != null && ret != DBNull.Value)
                            IDDocument = Convert.ToInt64(ret);
                    }
                    Suma += Convert.ToDecimal(dbRul["Val_finala"]);
                    SumaValuta += Convert.ToDecimal(dbRul["Val_valuta"]);
                    DateTime datai = Convert.ToDateTime(dbRul["data_doc"]);
                    cmd.CommandText = "INSERT INTO [Rulaje] ([ContDebit],[ContCredit],[CodValuta],[IDDocument],[ValoareValuta],[Curs],[ValoareLei],[DataDocJust],[TipDocJust],[NumarDocJust],[ExplicatiiDocJust],[NrCrt],[CodUnitate]) VALUES ('" +
                        numeD + "','" + numeC + "',0," + IDDocument.ToString() + "," +
                        Convert.ToDouble(dbRul["Val_valuta"]).ToString("F2", CultureInfo.InvariantCulture) + ",0," +
                        Convert.ToDouble(dbRul["Val_finala"]).ToString("F2", CultureInfo.InvariantCulture) + ",'" +
                        datai.Year.ToString() + "-" + datai.Month.ToString() + "-" + datai.Day.ToString() + "','" +
                        dbRul["tip_doc"].ToString().Trim() + "'," +
                        dbRul["nr_doc"].ToString().Trim() + ",'" +
                        dbRul["expl"].ToString().Trim() + "'," + NrCrt.ToString() + "," + ModuleInfo.CodUnitate.ToString() + ")";

                    cmd.ExecuteNonQuery();
                    pBar.Value++; // CultureInfo.InvariantCul
                    Application.DoEvents();
                }
                /// modifica ultimul document
                cmd.CommandText = "UPDATE [RulajeDoc]  SET [ValoareValuta] = " +
                      Convert.ToDouble(SumaValuta).ToString("F2", CultureInfo.InvariantCulture) + ", [ValoareLei]=" +
                      Convert.ToDouble(Suma).ToString("F2", CultureInfo.InvariantCulture) + " WHERE [IDDocument] = " + IDDocument.ToString();
                cmd.ExecuteNonQuery();

                tran.Commit();
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message.ToString() + ",\n" + cmd.CommandText);
                tran.Rollback();
                return false;
            }
            finally
            {
                if (dbRul != null)
                    dbRul.Close();
                dBaseConnection.Close();
            }


            dbRul.Close();
            dBaseConnection.Close();
            return true;

        }
        override public void SetCurentMonth(short vCodUnitate)
        {
            //Setup(conn);
        }

        private void Import_SQL(object sender, EventArgs e)
        {
            
        }
        /// <summary>
        /// cand se apasa enter, controlul activ este luna, anul
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        override public void toolStripLabelAnLuna_Click(object sender, EventArgs e)
        {

        }

        private void codUnitateCB_Enter(object sender, EventArgs e)
        {
            if (BazaWTB.Text == String.Empty)
            {
                MessageBox.Show("Completati baza de date!",
                        "Stop",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Stop);
                return;
            }
            codUnitateCB.Items.Clear();
            SqlCommand cmd1 = new SqlCommand("SELECT Distinct CodNod FROM " + (ServerWTB.Text == String.Empty ? "" : "[" + ServerWTB.Text + "].") + "[" + BazaWTB.Text + "].[dbo].[Cont]", DeschideConexiune(workConnString));
            SqlDataReader dr = cmd1.ExecuteReader();
            if (dr.HasRows)
            {
                while (dr.Read())
                {
                    codUnitateCB.Items.Add(dr["CodNod"]);
                }
            }
            dr.Close();
            InchideConexiune(cmd1.Connection);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = DeschideConexiune(workConnString);
            SqlCommand cmd = new SqlCommand("UPDATE [BalConSetup] SET [AnCurent] = " + PerioadaCurentaDTP.Value.Year.ToString()  + ",[LunaCurenta]=" + PerioadaCurentaDTP.Value.Month.ToString()  + " WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString(),
                con);

            cmd.ExecuteScalar();
            Object ret = GetSetupValue("BalconSetup", "LunaCurenta", ModuleInfo.CodUnitate, con);
            if (ret != null && ret != DBNull.Value)
                LunaCurenta = Convert.ToInt16(ret);
            ret = GetSetupValue("BalconSetup", "AnCurent", ModuleInfo.CodUnitate, con);
            if (ret != null && ret != DBNull.Value)
                AnulCurent = Convert.ToInt16(ret);
            InchideConexiune(con);
            MessageBoxFrm mbt = new MessageBoxFrm("Confirmare", "Modificarea perioadei curente s-a facut cu succes!", String.Empty, MessageBoxFrm.ImaginiMB.InformareIcon, null);
            mbt.AddButton("Ieşire", 70, mbt.MessageBoxFrm_Click_Yes);
            mbt.ShowDialog();
        }

    }
}
